from fastapi import APIRouter, HTTPException, Query
from typing import List, Optional
from models.database import get_db_connection
from models.schemas import (
    GoodsResponse,
    PaginatedGoodsResponse,
    SearchGoodsResponse,
    GoodsSearch
)
import mysql.connector

router = APIRouter(prefix="/goods", tags=["商品管理"])


@router.get("/", response_model=PaginatedGoodsResponse)
async def get_goods_list(
        sort_by: Optional[str] = Query(None, description="排序字段: sell_num-销量, review_rate-好评率, price-价格"),
        order: Optional[str] = Query("desc", description="排序顺序: asc-升序, desc-降序"),
        page: int = Query(1, ge=1, description="页码"),
        page_size: int = Query(10, ge=1, le=100, description="每页数量"),
        min_price: Optional[float] = Query(None, ge=0, description="最低价格"),
        max_price: Optional[float] = Query(None, ge=0, description="最高价格"),
        min_rating: Optional[float] = Query(None, ge=0, le=1, description="最低好评率(0-1)"),
        in_stock: Optional[bool] = Query(None, description="仅显示有库存商品")
):
    """获取商品列表，支持排序、分页和筛选"""
    connection = get_db_connection()
    if not connection:
        raise HTTPException(status_code=500, detail="数据库连接失败")

    try:
        cursor = connection.cursor(dictionary=True)
        offset = (page - 1) * page_size

        # 构建WHERE条件
        where_conditions = ["1=1"]
        params = []

        # 价格筛选
        if min_price is not None:
            where_conditions.append("good_sell_price >= %s")
            params.append(min_price)
        if max_price is not None:
            where_conditions.append("good_sell_price <= %s")
            params.append(max_price)

        # 好评率筛选
        if min_rating is not None:
            where_conditions.append("good_positive_review_rate >= %s")
            params.append(min_rating)

        # 库存筛选
        if in_stock:
            where_conditions.append("good_number > 0")

        where_clause = " AND ".join(where_conditions)

        # 构建排序条件
        order_mapping = {
            "sell_num": "good_sell_num",
            "review_rate": "good_positive_review_rate",
            "price": "good_sell_price"
        }

        order_field = order_mapping.get(sort_by, "good_id")
        order_direction = "ASC" if order == "asc" else "DESC"
        order_by = f"ORDER BY {order_field} {order_direction}"

        # 查询商品数据 - 移除 good_purchase_price 字段
        query = f"""
            SELECT good_id, good_name, good_number, good_sell_num, 
                   good_positive_review_rate, good_sell_price, good_description
            FROM goods
            WHERE {where_clause}
            {order_by}
            LIMIT %s OFFSET %s
        """

        params.extend([page_size, offset])
        cursor.execute(query, params)
        goods = cursor.fetchall()

        # 查询总数
        count_query = f"SELECT COUNT(*) as total FROM goods WHERE {where_clause}"
        cursor.execute(count_query, params[:-2])  # 移除LIMIT和OFFSET参数
        total = cursor.fetchone()['total']

        return PaginatedGoodsResponse(
            goods=goods,
            pagination={
                "page": page,
                "page_size": page_size,
                "total": total,
                "pages": (total + page_size - 1) // page_size,
                "has_prev": page > 1,
                "has_next": page * page_size < total
            }
        )

    except mysql.connector.Error as e:
        raise HTTPException(status_code=500, detail=f"数据库错误: {e}")
    finally:
        cursor.close()
        connection.close()


@router.get("/top-rated", response_model=List[GoodsResponse])
async def get_top_rated_goods(
        limit: int = Query(3, ge=1, le=20, description="返回数量"),
        min_rating: float = Query(0.8, ge=0, le=1, description="最低好评率")
):
    """获取好评率最高的商品"""
    connection = get_db_connection()
    if not connection:
        raise HTTPException(status_code=500, detail="数据库连接失败")

    try:
        cursor = connection.cursor(dictionary=True)
        cursor.execute("""
            SELECT good_id, good_name, good_number, good_sell_num, 
                   good_positive_review_rate, good_sell_price, good_description
            FROM goods
            WHERE good_positive_review_rate >= %s AND good_number > 0
            ORDER BY good_positive_review_rate DESC, good_sell_num DESC
            LIMIT %s
        """, (min_rating, limit))

        goods = cursor.fetchall()
        return goods

    except mysql.connector.Error as e:
        raise HTTPException(status_code=500, detail=f"数据库错误: {e}")
    finally:
        cursor.close()
        connection.close()


@router.get("/search", response_model=SearchGoodsResponse)
async def search_goods(
        keyword: Optional[str] = Query(None, description="搜索关键词(商品名称或描述)"),
        good_id: Optional[int] = Query(None, description="商品ID"),
        min_price: Optional[float] = Query(None, ge=0, description="最低价格"),
        max_price: Optional[float] = Query(None, ge=0, description="最高价格"),
        min_rating: Optional[float] = Query(None, ge=0, le=1, description="最低好评率"),
        in_stock: Optional[bool] = Query(None, description="仅显示有库存"),
        sort_by: Optional[str] = Query("sell_num",
                                       description="排序字段: sell_num-销量, review_rate-好评率, price-价格"),
        order: Optional[str] = Query("desc", description="排序顺序: asc-升序, desc-降序")
):
    """高级搜索商品（支持多条件筛选）"""
    connection = get_db_connection()
    if not connection:
        raise HTTPException(status_code=500, detail="数据库连接失败")

    try:
        cursor = connection.cursor(dictionary=True)

        # 构建WHERE条件
        where_conditions = ["1=1"]
        params = []

        # 强化关键词搜索逻辑 - 支持品牌名模糊搜索
        if keyword:
            # 使用多个LIKE条件提高搜索匹配度
            keyword_conditions = [
                "good_name LIKE %s",
                "good_description LIKE %s"
            ]
            # 为每个条件添加相同的参数
            where_conditions.append(f"({' OR '.join(keyword_conditions)})")
            params.extend([f"%{keyword}%", f"%{keyword}%"])

        # 商品ID精确搜索
        if good_id:
            where_conditions.append("good_id = %s")
            params.append(good_id)

        # 价格范围筛选
        if min_price is not None:
            where_conditions.append("good_sell_price >= %s")
            params.append(min_price)
        if max_price is not None:
            where_conditions.append("good_sell_price <= %s")
            params.append(max_price)

        # 好评率筛选
        if min_rating is not None:
            where_conditions.append("good_positive_review_rate >= %s")
            params.append(min_rating)

        # 库存筛选
        if in_stock:
            where_conditions.append("good_number > 0")

        where_clause = " AND ".join(where_conditions)

        # 构建排序条件
        order_mapping = {
            "sell_num": "good_sell_num",
            "review_rate": "good_positive_review_rate",
            "price": "good_sell_price"
        }
        order_field = order_mapping.get(sort_by, "good_sell_num")
        order_direction = "ASC" if order == "asc" else "DESC"

        # 查询商品数据 - 移除 good_purchase_price 字段
        query = f"""
            SELECT good_id, good_name, good_number, good_sell_num, 
                   good_positive_review_rate, good_sell_price, good_description
            FROM goods
            WHERE {where_clause}
            ORDER BY {order_field} {order_direction}
        """

        cursor.execute(query, params)
        goods = cursor.fetchall()

        # 查询总数
        count_query = f"SELECT COUNT(*) as total FROM goods WHERE {where_clause}"
        cursor.execute(count_query, params)
        total = cursor.fetchone()['total']

        if not goods:
            raise HTTPException(status_code=404, detail="未找到相关商品")

        return SearchGoodsResponse(
            goods=goods,
            total=total,
            filters={
                "keyword": keyword,
                "good_id": good_id,
                "price_range": {
                    "min": min_price,
                    "max": max_price
                },
                "min_rating": min_rating,
                "in_stock": in_stock
            }
        )

    except mysql.connector.Error as e:
        raise HTTPException(status_code=500, detail=f"数据库错误: {e}")
    finally:
        cursor.close()
        connection.close()


@router.get("/{good_id}", response_model=GoodsResponse)
async def get_good_detail(good_id: int):
    """获取商品详情"""
    connection = get_db_connection()
    if not connection:
        raise HTTPException(status_code=500, detail="数据库连接失败")

    try:
        cursor = connection.cursor(dictionary=True)
        cursor.execute("""
            SELECT good_id, good_name, good_number, good_sell_num, 
                   good_positive_review_rate, good_sell_price, good_description
            FROM goods
            WHERE good_id = %s
        """, (good_id,))

        product = cursor.fetchone()
        if not product:
            raise HTTPException(status_code=404, detail="商品不存在")

        return product

    except mysql.connector.Error as e:
        raise HTTPException(status_code=500, detail=f"数据库错误: {e}")
    finally:
        cursor.close()
        connection.close()